package com.arvin.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.HashSet;

@Mapper
public interface DataCountMapper {
    /**
     * 获取报名人员年级分布情况
     * @param grade
     * @param className
     * @return
     */
    @Select("SELECT COUNT(*) FROM t_stuinformation WHERE stu_grade = #{grade} and class_name = #{className}")
    int getInClassDivideByGrade(String grade, String className);

    /**
     * 获取卓越班报名人数
     * @param className
     * @return
     */
    @Select("SELECT COUNT(*) FROM t_stuinformation where class_name = #{className}")
    Integer getTotalApplication(String className);

    /**
     * 获取整个卓越班人数
     * @param className
     * @return
     */
    @Select("SELECT COUNT(*) FROM t_stuinformation s " +
            "left join t_interview i on s.intv_id = i.intv_id " +
            "where 1=1 and intv_state = '已通过' and class_name = #{className} ")
    Integer getTotalZYClass(String className);

    /**
     * 获取参加考试人数
     * @param className
     * @return
     */
    @Select("SELECT COUNT(*) FROM t_exam e " +
            "left join t_stuinformation s on e.exam_id = s.exam_id " +
            "WHERE 1=1 and exam_state != '未考试' and class_name = #{className}")
    Integer getJoinExam(String className);

    /**
     * 通过考试状态获取人数
     * @param state
     * @param className
     * @return
     */
    @Select("SELECT COUNT(*) FROM t_exam e " +
            "left join t_stuinformation s on e.exam_id = s.exam_id " +
            "WHERE 1=1 and exam_state = #{state} and class_name = #{className}")
    Integer getExamByState(String state, String className);

    /**
     * 获取参加面试人数
     * @param className
     * @return
     */
    @Select("SELECT COUNT(*) FROM t_interview i " +
            "left join t_stuinformation s on i.intv_id = s.intv_id " +
            "WHERE 1=1 and intv_state != '未面试' and class_name = #{className}")
    Integer getjoinIntv(String className);

    /**
     * 通过面试状态统计人数
     * @param state
     * @param className
     * @return
     */
    @Select("SELECT COUNT(*) FROM t_interview i " +
            "left join t_stuinformation s on i.intv_id = s.intv_id " +
            "WHERE 1=1 and intv_state = #{state} and class_name = #{className}")
    Integer getIntvByState(String state, String className);

    /**
     * 获取申请免试人员数量
     * @param className
     * @return
     */
    @Select("SELECT COUNT(*) FROM t_exempt e " +
            "left join t_stuinformation s on e.exem_id = s.exem_id " +
            "WHERE 1=1 and class_name = #{className}")
    Integer getAskExem(String className);

    /**
     * 通过免试状态分别统计
     * @param state
     * @param className
     * @return
     */
    @Select("SELECT COUNT(*) FROM t_exempt e " +
            "left join t_stuinformation s on e.exem_id = s.exem_id " +
            "WHERE 1=1 and exem_state = #{state} and class_name = #{className}")
    Integer getExemByState(String state, String className);

    /**
     * 获取学生的专业
     * @return
     */
    @Select("select stu_speciality from t_stuinformation where class_name = #{className}")
    HashSet<String> getAllStudentSpecialty(String className);

    /**
     * 获取专业分布人数
     * @param special
     * @param className
     * @return
     */
    @Select("SELECT COUNT(*) FROM `t_stuinformation` WHERE stu_speciality = #{special} and class_name = #{className}")
    Integer getDivideBySpecial(String special, String className);

    /**
     * 获取笔试分数分布情况
     * @param className
     * @param startScore
     * @param endScore
     * @return
     */
    @Select("SELECT COUNT(*) FROM `t_exam` e " +
            "LEFT JOIN t_stuinformation s ON e.student_id = s.student_id " +
            "where 1=1 and s.class_name = #{className} " +
            "and e.exam_score BETWEEN #{startScore} AND #{endScore}")
    Integer getExamScoreCount(String className, int startScore, int endScore);

    /**
     * 获取笔试状态统计
     * @param className
     * @param categoryItem
     * @return
     */
    @Select("SELECT COUNT(*) FROM `t_exam` e " +
            "LEFT JOIN t_stuinformation s ON e.student_id = s.student_id " +
            "where 1=1 and s.class_name = #{className} and e.exam_state = #{categoryItem} ")
    Integer getExamStateCount(String className, String categoryItem);

    /**
     * 获取面试分数分数段人数
     * @param className
     * @param startScore
     * @param endScore
     * @return
     */
    @Select("SELECT COUNT(*) FROM `t_interview` i " +
            "LEFT JOIN t_stuinformation s ON i.student_id = s.student_id " +
            "where 1=1 and s.class_name = #{className} " +
            "and i.intv_score BETWEEN #{startScore} AND #{endScore}")
    Integer getIntvScoreCount(String className, int startScore, int endScore);

    /**
     * 获取面试状态统计
     * @param className
     * @param stateItem
     * @return
     */
    @Select("SELECT COUNT(*) FROM `t_interview` i " +
            "LEFT JOIN t_stuinformation s ON i.student_id = s.student_id " +
            "where 1=1 and s.class_name = #{className} and i.intv_state = #{stateItem} ")
    Integer getIntvStateCount(String className, String stateItem);

    /**
     * 获取所有卓越班班级
     * @return
     */
    @Select("SELECT class_name FROM `t_stuinformation`")
    HashSet<String> getAllClassName();

    /**
     * 获取入班学生班级名称
     * @param className
     * @return
     */
    @Select("select stu_class FROM `t_stuinformation` where class_name = #{className}")
    HashSet<String> getInClassStudentClass(String className);

    /**
     * 获取入班学生班级分布数量
     * @param className
     * @param stuClassName
     * @return
     */
    @Select("SELECT COUNT(*) FROM `t_stuinformation` s " +
            "left join t_interview i on s.intv_id = i.intv_id " +
            "where 1=1 and intv_state = '已通过' and class_name = #{className} and stu_class = #{stuClassName}")
    Integer getNumByStudentClassName(String className, String stuClassName);
}
